# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
工具类，实现了对参数进行组合，生产不同组合的dict类型的参数，并将dict参数保存到list中，方便取用。 
1 为测试报表选择性进件；EXCEL第一个sheet
2 进件字段校验(必填和非必填);EXCEL第二个sheet
3 为测试形式审批选择性进件；EXCEL第三个sheet
4 为测试客户状态查询；EXCEL第四个sheet  ,先执行进件字段校验，支付回调再执行 进件审批，最后执行客户状态查询

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
import xlrd
from qdzjmsloan_api import API_QDZJ
import copy
import pymysql
from datetime import datetime
from xlrd import xldate_as_tuple


class parameter():
    # 可以通过更改渠道名称去测试具体渠道(渠道代码不匹配的渠道不测试)
    t = '1'
    hy_channel= t + 'BJHY'        # 北京华域
    gf_channel = t + 'M008'       # 贵丰项目M008
    jrb_channel = t +  'M009'       # 佳融宝项目
    zh_channel = t + 'BJZH'       # 北京众禾
    ssr_channel = t + 'M012'      # 深盛融项目
    gh_channel = t + 'M015'       # 固赫智能项目
    qh_channel = t + 'M016'           # 前海微贷项目
    yk_channel = t + 'M017'        # 云科贷项目
    bd_channel = t +  'M018'        # 百多项目
    md_channel = t +  'M023'        # 马达项目(百多子项目)
    cm_channel = t +  'M024'        # 昌淼项目(百多子项目)
    xy_channel = t + 'M019'        # 小业金融项目
    fy_channel = t + 'M020'        # 阜阳昌盛项目
    wr_channel = 'M021'        # 微融（国通贷款）项目
    hr_channel = 'M022'        # 海融项目
    ly_channel = t + 'SDLY'
    ws_channel = t + 'WHZH'

    db = pymysql.connect(
        host="10.16.20.182",  # 测试环境
        # host="10.16.20.190",     # 开发环境
        user="dev",  # 测试：dev   开发：root
        password="dev",  # 测试：dev   开发：123456
        port=3306,
        use_unicode=True,
        charset="utf8",
        database="qdzjmsloandb")


    # 为测试报表选择性进件；EXCEL第一个sheet
    def get_report_data(self,xlsFile):
        cursor = self.db.cursor()
        sql = "select business_date from system_date ;"
        cursor.execute(sql)
        results = cursor.fetchone()
        self.db.close()
        bus_date = str(results[0])
        # date = time.mktime(time.strptime(str(results[0]),'%Y-%m-%d'))     # 转化为浮点型
        # print(type(str(results[0])), results[0])
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[0]
        nrows = table.nrows
        cellData = []
        # 为了不更改原始报文内容
        hy_Api = copy.deepcopy(API_QDZJ['BJHY'])
        zh_Api = copy.deepcopy(API_QDZJ['BJZH'])
        gf_Api = copy.deepcopy(API_QDZJ['M008'])
        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            channel = str(table.cell_value(i, 2))[:4]
            try:
                date = datetime(*xldate_as_tuple(table.cell_value(i, 7), 0)).strftime('%Y-%m-%d')
                if channel == self.hy_channel and date == bus_date:
                    BJHY_list = hy_Api['service']['serviceBody']['request']
                    BJHY_list['caseId'] = table.cell_value(i, 2)
                    BJHY_list['creditLimit'] = table.cell_value(i, 3)
                    BJHY_copy = copy.deepcopy(hy_Api)  # 深拷贝
                    row.append(BJHY_copy)
                    try:
                        status = str(table.cell_value(i, 13))
                        row.append(status)
                    except:
                        status = table.cell_value(i, 13)
                        row.append(status)
                    cellData.append(row)

                elif channel == self.zh_channel and date == bus_date:
                    BJZH_list = zh_Api['service']['serviceBody']['request']
                    BJZH_list['caseId'] = table.cell_value(i, 2)
                    BJZH_list['creditLimit'] = table.cell_value(i, 3)
                    BJZH_copy = copy.deepcopy(zh_Api)  # 深拷贝
                    row.append(BJZH_copy)
                    try:
                        status = str(table.cell_value(i, 13))
                        row.append(status)
                    except:
                        status = table.cell_value(i, 13)
                        row.append(status)
                    cellData.append(row)

                elif channel == self.gf_channel and date == bus_date:
                    M008_list = gf_Api['service']['serviceBody']['request']
                    M008_list['caseId'] = table.cell_value(i, 2)
                    M008_list['creditLimit'] = table.cell_value(i, 3)
                    M008_copy = copy.deepcopy(gf_Api)  # 深拷贝
                    row.append(M008_copy)
                    try:
                        status = str(table.cell_value(i, 13))
                        row.append(status)
                    except:
                        status = table.cell_value(i, 13)
                        row.append(status)
                    cellData.append(row)

                elif channel == self.ly_channel and date == bus_date:
                    SDLY_list = API_QDZJ['SDLY']['service']['serviceBody']['request']
                    SDLY_list['caseId'] = table.cell_value(i, 2)
                    SDLY_list['creditLimit'] = table.cell_value(i, 3)
                    SDLY_copy = copy.deepcopy(API_QDZJ['SDLY'])  # 深拷贝
                    row.append(SDLY_copy)
                    try:
                        status = str(table.cell_value(i, 13))
                        row.append(status)
                    except:
                        status = table.cell_value(i, 13)
                        row.append(status)
                    cellData.append(row)

                elif channel == self.ws_channel and date == bus_date:
                    WHZH_list = API_QDZJ['WHZHWS']['service']['serviceBody']['request']
                    WHZH_list['caseId'] = table.cell_value(i, 2)
                    WHZH_list['creditLimit'] = table.cell_value(i, 3)
                    WHZH_copy = copy.deepcopy(API_QDZJ['WHZHWS'])  # 深拷贝
                    row.append(WHZH_copy)
                    try:
                        status = str(table.cell_value(i, 13))
                        row.append(status)
                    except:
                        status = table.cell_value(i, 13)
                        row.append(status)
                    cellData.append(row)
            except:
                pass
        # print(cellData)
        return cellData


    # 进件字段校验(必填和非必填);EXCEL第二个sheet
    def get_jinjian_data(self,xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[1]
        nrows = table.nrows
        cellData = []
        # 为了不更改原始报文内容
        hy_Api = copy.deepcopy(API_QDZJ['BJHY'])           # 华域项目
        zh_Api = copy.deepcopy(API_QDZJ['BJZH'])           # 众禾项目
        gf_Api = copy.deepcopy(API_QDZJ['M008'])           # 贵丰项目
        jrb_Api = copy.deepcopy(API_QDZJ['M009'])          # 佳融宝项目
        ssr_Api = copy.deepcopy(API_QDZJ['M012'])          # 深盛融项目
        gh_Api = copy.deepcopy(API_QDZJ['M015'])           # 固赫智能项目
        qh_Api = copy.deepcopy(API_QDZJ['M016'])           # 前海微贷项目
        yk_Api = copy.deepcopy(API_QDZJ['M017'])           # 云科贷项目
        bd_Api = copy.deepcopy(API_QDZJ['M018'])           # 百多项目
        md_Api = copy.deepcopy(API_QDZJ['M018'])           # 马达项目(百多子项目)
        cm_Api = copy.deepcopy(API_QDZJ['M018'])           # 昌淼项目(百多子项目)
        xyy_Api = copy.deepcopy(API_QDZJ['M019y'])         # 小业金融项目360天
        xym_Api = copy.deepcopy(API_QDZJ['M019m'])         # 小业金融项目30天
        fy_Api = copy.deepcopy(API_QDZJ['M020'])           # 阜阳昌盛项目
        wr_Api = copy.deepcopy(API_QDZJ['M021'])           # 微融（国通贷款）项目
        hr_Api = copy.deepcopy(API_QDZJ['M022'])           # 海融项目

        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            channel = str(table.cell_value(i, 4))[:4]
            idCard = table.cell_value(i, 10)
            if channel == self.hy_channel:
                list = hy_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = hy_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                qdzj_list['creditAuthUrl'] = 'https://msbuc.oss-cn-shenzhen.aliyuncs.com/test/meson/lyloan/picture/personal_info/2018-11-30/WechatIMG34.jpeg'
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                select_cs = table.cell_value(i, 7)    # 多个参数必填一个
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                elif select_cs != '':
                    qdzj_list[select_cs] = 'https://msbuc.oss-cn-shenzhen.aliyuncs.com/test/meson/lyloan/picture/personal_info/2018-11-30/WechatIMG34.jpeg'
                    qdzj_list['creditAuthUrl'] = ''
                BJHY_copy = copy.deepcopy(hy_Api)  # 深拷贝
                row.append(BJHY_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                qdzj_list[select_cs] = ''
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.zh_channel:                    # 渠道众禾
                list = zh_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = zh_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                qdzj_list['creditAuthUrl'] = 'https://msbuc.oss-cn-shenzhen.aliyuncs.com/test/meson/lyloan/picture/personal_info/2018-11-30/WechatIMG34.jpeg'
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                select_cs = table.cell_value(i, 7)    # 多个参数必填一个
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                elif select_cs != '':
                    qdzj_list[select_cs] = 'https://msbuc.oss-cn-shenzhen.aliyuncs.com/test/meson/lyloan/picture/personal_info/2018-11-30/WechatIMG34.jpeg'
                    qdzj_list['creditAuthUrl'] = ''
                qdzj_copy = copy.deepcopy(zh_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                qdzj_list[select_cs] = ''
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.gf_channel:                    # 贵丰渠道
                list = gf_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = gf_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                qdzj_list['creditAuthUrl'] = 'https://msbuc.oss-cn-shenzhen.aliyuncs.com/test/meson/lyloan/picture/personal_info/2018-11-30/WechatIMG34.jpeg'
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                select_cs = table.cell_value(i, 7)    # 多个参数必填一个
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                elif select_cs != '':
                    qdzj_list[select_cs] = 'https://msbuc.oss-cn-shenzhen.aliyuncs.com/test/meson/lyloan/picture/personal_info/2018-11-30/WechatIMG34.jpeg'
                    qdzj_list['creditAuthUrl'] = ''
                BJHY_copy = copy.deepcopy(gf_Api)  # 深拷贝
                row.append(BJHY_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                qdzj_list[select_cs] = ''
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.jrb_channel:                    # 佳融宝项目
                list = jrb_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = jrb_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                select_cs = table.cell_value(i, 7)    # 多个参数必填一个
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(jrb_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                qdzj_list[select_cs] = ''
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.ssr_channel:                    # 深盛融项目
                list = ssr_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = ssr_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                select_cs = table.cell_value(i, 7)    # 多个参数必填一个
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(ssr_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                qdzj_list[select_cs] = ''
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.gh_channel:                    # 固赫智能项目项目
                list = gh_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = gh_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                select_cs = table.cell_value(i, 7)    # 多个参数必填一个
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(gh_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                qdzj_list[select_cs] = ''
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.qh_channel:                      # 前海微贷项目
                list = qh_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = qh_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(qh_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.yk_channel:                      # 云科贷项目
                list = yk_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = yk_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(yk_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.bd_channel:                    # 百多项目
                list = bd_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = bd_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(bd_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.md_channel:                    # 马达项目(百多子项目)
                list = md_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = md_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(md_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.cm_channel:                    # 昌淼项目(百多子项目)
                list = cm_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = cm_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(cm_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.xy_channel and str(table.cell_value(i, 4))[0:5] == 'M019y':    # 小业金融项目360天
                list = xyy_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = xyy_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(xyy_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.xy_channel and str(table.cell_value(i, 4))[0:5] == 'M019m':     # 小业金融项目30天
                list = xym_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = xym_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(xym_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.fy_channel:                      # 阜阳昌盛项目M020
                list = fy_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = fy_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(fy_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.wr_channel:                      # 微融（国通贷款）项目M021
                list = wr_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = wr_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(wr_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.hr_channel:                      # 海融项目
                list = hr_Api['service']['serviceHeader']
                list['channelCd'] = table.cell_value(i, 2)
                list['productCd'] = table.cell_value(i, 3)
                qdzj_list = hr_Api['service']['serviceBody']['request']
                qdzj_list['idCard'] = idCard
                qdzj_list['caseId'] = table.cell_value(i, 4)
                qdzj_list['creditResult'] = table.cell_value(i, 9)
                must_cs = table.cell_value(i, 5)      # 必填参数
                not_cs = table.cell_value(i, 6)       # 非必填参数
                temp_must = None                      # 创建一个临时变量将设为空的字段还原
                temp_not = None
                if must_cs !='' and not_cs == '':
                    temp_must = qdzj_list[must_cs]
                    qdzj_list[must_cs] = None
                elif not_cs !='' and must_cs =='' :
                    temp_not = qdzj_list[not_cs]
                    qdzj_list[not_cs] = None
                qdzj_copy = copy.deepcopy(hr_Api)  # 深拷贝
                row.append(qdzj_copy)
                qdzj_list[must_cs] = temp_must
                qdzj_list[not_cs] = temp_not
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

        #print("总个数为：",len(cellData),cellData)
        return cellData


    # 为测试形式审批选择性进件；EXCEL第三个sheet
    def get_approval_data(self,xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[2]
        nrows = table.nrows
        cellData = []
        # 为了不更改原始报文内容
        hy_Api = copy.deepcopy(API_QDZJ['BJHY'])           # 华域项目
        zh_Api = copy.deepcopy(API_QDZJ['BJZH'])           # 众禾项目
        gf_Api = copy.deepcopy(API_QDZJ['M008'])           # 贵丰项目
        jrb_Api = copy.deepcopy(API_QDZJ['M009'])          # 佳融宝项目
        ssr_Api = copy.deepcopy(API_QDZJ['M012'])          # 深盛融项目
        gh_Api = copy.deepcopy(API_QDZJ['M015'])           # 固赫智能项目项目
        qh_Api = copy.deepcopy(API_QDZJ['M016'])           # 前海微贷项目
        yk_Api = copy.deepcopy(API_QDZJ['M017'])           # 云科贷项目
        bd_Api = copy.deepcopy(API_QDZJ['M018'])           # 百多项目
        md_Api = copy.deepcopy(API_QDZJ['M018'])           # 马达项目(百多子项目)
        cm_Api = copy.deepcopy(API_QDZJ['M018'])           # 昌淼项目(百多子项目)
        xyy_Api = copy.deepcopy(API_QDZJ['M019y'])         # 小业金融项目360天
        xym_Api = copy.deepcopy(API_QDZJ['M019m'])         # 小业金融项目30天
        fy_Api = copy.deepcopy(API_QDZJ['M020'])           # 阜阳昌盛项目
        wr_Api = copy.deepcopy(API_QDZJ['M021'])           # 微融（国通贷款）项目
        hr_Api = copy.deepcopy(API_QDZJ['M022'])           # 海融项目

        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            channel = str(table.cell_value(i, 1))[0:4]
            if channel == self.hy_channel:
                qdzj_list = hy_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                BJHY_copy = copy.deepcopy(hy_Api)  # 深拷贝
                row.append(BJHY_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.zh_channel:
                qdzj_list = zh_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                BJZH_copy = copy.deepcopy(zh_Api)  # 深拷贝
                row.append(BJZH_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.gf_channel:       # 贵丰渠道
                qdzj_list = gf_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(gf_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.jrb_channel:                           # 佳融宝项目
                list = jrb_Api['service']['serviceHeader']
                list['productCd'] = table.cell_value(i, 11)
                qdzj_list = jrb_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(jrb_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.ssr_channel:       # 深盛融项目
                qdzj_list = ssr_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(ssr_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.gh_channel:                            # 固赫智能项目项目
                qdzj_list = gh_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(gh_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.qh_channel:                           # 前海微贷项目
                list = qh_Api['service']['serviceHeader']
                list['productCd'] = table.cell_value(i, 11)
                qdzj_list = qh_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(qh_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.yk_channel:                           # 云科贷项目
                list = yk_Api['service']['serviceHeader']
                list['productCd'] = table.cell_value(i, 11)
                qdzj_list = yk_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(yk_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.bd_channel:                           # 百多项目M018
                list = bd_Api['service']['serviceHeader']
                list['productCd'] = table.cell_value(i, 11)
                qdzj_list = bd_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(bd_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.md_channel:                           # 马达项目(百多子项目)M023
                list = md_Api['service']['serviceHeader']
                list['channelCd'] = 'M023'
                list['productCd'] = table.cell_value(i, 11)
                qdzj_list = md_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(md_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.cm_channel:                           # 昌淼项目(百多子项目)M024
                list = cm_Api['service']['serviceHeader']
                list['channelCd'] = 'M024'
                list['productCd'] = table.cell_value(i, 11)
                qdzj_list = cm_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(cm_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.xy_channel :    # 小业金融项目
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                product = table.cell_value(i, 11)
                if product == 'PCD82':
                    list = xyy_Api['service']['serviceHeader']
                    list['productCd'] = product
                    qdzj_list = xyy_Api['service']['serviceBody']['request']
                    qdzj_list['caseId'] = case_id
                    qdzj_list['idCard'] = table.cell_value(i, 2)
                    qdzj_list['age'] = int(table.cell_value(i, 3))
                    qdzj_list['creditLimit'] = table.cell_value(i, 4)
                    qdzj_list['repayPattern'] = table.cell_value(i, 5)
                    qdzj_list['creditRate'] = table.cell_value(i, 6)
                    qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                    qdzj_list['cardNo'] = table.cell_value(i, 10)
                    qdzj_copy = copy.deepcopy(xyy_Api)  # 深拷贝
                else:
                    list = xym_Api['service']['serviceHeader']
                    list['productCd'] = product
                    qdzj_list = xym_Api['service']['serviceBody']['request']
                    qdzj_list['caseId'] = case_id
                    qdzj_list['idCard'] = table.cell_value(i, 2)
                    qdzj_list['age'] = int(table.cell_value(i, 3))
                    qdzj_list['creditLimit'] = table.cell_value(i, 4)
                    qdzj_list['repayPattern'] = table.cell_value(i, 5)
                    qdzj_list['creditRate'] = table.cell_value(i, 6)
                    qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                    qdzj_list['cardNo'] = table.cell_value(i, 10)
                    qdzj_copy = copy.deepcopy(xym_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.fy_channel:                           # 阜阳昌盛项目
                list = fy_Api['service']['serviceHeader']
                list['productCd'] = table.cell_value(i, 11)
                qdzj_list = fy_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(fy_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.wr_channel:                           # 微融（国通贷款）项目M021
                list = wr_Api['service']['serviceHeader']
                list['productCd'] = table.cell_value(i, 11)
                qdzj_list = wr_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(wr_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

            elif channel == self.hr_channel:                           # 海融项目
                list = hr_Api['service']['serviceHeader']
                list['productCd'] = table.cell_value(i, 11)
                qdzj_list = hr_Api['service']['serviceBody']['request']
                case_id = table.cell_value(i, 1)
                case_result = table.cell_value(i, 9)
                row.append(case_id)
                row.append(case_result)
                qdzj_list['caseId'] = case_id
                qdzj_list['idCard'] = table.cell_value(i, 2)
                qdzj_list['age'] = int(table.cell_value(i, 3))
                qdzj_list['creditLimit'] = table.cell_value(i, 4)
                qdzj_list['repayPattern'] = table.cell_value(i, 5)
                qdzj_list['creditRate'] = table.cell_value(i, 6)
                qdzj_list['creditTerm'] = int(table.cell_value(i, 7))
                qdzj_list['cardNo'] = table.cell_value(i, 10)
                qdzj_copy = copy.deepcopy(hr_Api)  # 深拷贝
                row.append(qdzj_copy)
                try:
                    status = str(table.cell_value(i, 8))
                    row.append(status)
                except:
                    status = table.cell_value(i, 8)
                    row.append(status)
                cellData.append(row)

        #print("总个数为：", len(cellData), cellData)
        return cellData


    # 为测试客户状态查询；EXCEL第四个sheet  ,先执行进件字段校验，支付回调再执行 进件审批，最后执行客户状态查询(pay_order表，支付成功、支付失败、支付中)
    def get_inquiry_data(self,xlsFile):
        data = xlrd.open_workbook(xlsFile)
        table = data.sheets()[3]
        nrows = table.nrows
        cellData = []
        # 获取指定列数据
        for i in range(1, nrows):
            row = []
            channel = str(table.cell_value(i, 1))[0:4]
            if channel == self.hy_channel:
                BJHY_list = API_QDZJ['hyQuery']['service']['serviceBody']['request']
                BJHY_list['caseId'] = table.cell_value(i, 1)
                BJHY_list['idCard'] = table.cell_value(i, 2)
                BJHY_copy = copy.deepcopy(API_QDZJ['hyQuery'])  # 深拷贝
                row.append(BJHY_copy)
                result = str(table.cell_value(i, 3))
                status = str(table.cell_value(i, 4))
                row.append(result)
                row.append(status)
                cellData.append(row)
            elif channel == self.gf_channel:
                gf_list = API_QDZJ['gfQuery']['service']['serviceBody']['request']
                gf_list['caseId'] = table.cell_value(i, 1)
                gf_list['idCard'] = table.cell_value(i, 2)
                gf_copy = copy.deepcopy(API_QDZJ['gfQuery'])  # 深拷贝
                row.append(gf_copy)
                result = str(table.cell_value(i, 3))
                status = str(table.cell_value(i, 4))
                row.append(result)
                row.append(status)
                cellData.append(row)
        #print(cellData)
        return cellData


if __name__ == '__main__':
    data = parameter()
    data.get_approval_data('../files/qdzjmsloan.xls')
    #data.get_jinjian_data('../files/qdzjmsloan.xls')